//
//  FMDBDao.m
//  MyNotes
//
//  Created by chen on 17/4/5.
//  Copyright © 2017年 chen. All rights reserved.
//

#import "FMDBDao.h"

#define DBFILE_NAME_PERSON @"NotesList.db"
#define DBFILE_NAME_NOTE @"note.db"
#define DBFILE_NAME_TYPE @"type.db"
//PERSON表的增删改查
#define CREAT_TABLE_PERSON @"CREATE TABLE IF NOT EXISTS person(cid integer primary key autoincrement, name text, age integer)"
#define CREAT_TABLE_PERSON_INSERT @"INSERT INTO person(name,age) VALUES('%@','%ld')"
#define CREAT_TABLE_PERSON_DELETEBYID @"delete from person where cid = %ld"
#define CREAT_TABLE_PERSON_QUERYALL @"select name, age from person"
#define CREAT_TABLE_PERSON_UPDATEBYCID @"update person set name = 'chenbinghui' where cid = %ld"
//NOTE表的增删改查
#define CREAT_TABLE_NOTE @"CREATE TABLE IF NOT EXISTS note(cid integer primary key autoincrement, content text, date text,tid integer)"
#define INSERT_TABLE_NOTE @"INSERT INTO note(content,date,tid) VALUES('%@','%@','%ld')"
#define DELETE_TABLE_NOTE_BYID @"delete from note where cid = %ld"
#define QUERY_TABLE_NOTE_BYCID @"select * from note where cid = %ld"
#define QUERY_TABLE_NOTE_BYTID @"select * from note where tid = %ld"
#define UPDATE_TABLE_NOTE_BYCID @"update note set content = %@ where cid = %ld"
//TYPE表的增删改查
#define CREAT_TABLE_TYPE @"CREATE TABLE IF NOT EXISTS type(tid integer primary key autoincrement, tname text,note1 text default '',note2 text default '')"
#define INSERT_TABLE_TYPE @"INSERT INTO type(tname) VALUES('%@')"
#define QUERY_TABLE_TYPE @"select a.tid,a.tname,count(b.cid) as num from type a LEFT JOIN note b on a.tid = b.tid group by a.tid"
#define QUERY_TABLE_TYPE_BYALL @"select tname,tid from type"
#define DELETE_TABLE_TYPE_BYID @"delete from type where tid = %ld"

@interface FMDBDao()
@property(nonatomic,strong) FMDatabase *database;
@property(nonatomic,strong) NSString *dbName;
@end

@implementation FMDBDao
static FMDBDao *dao = nil;

+ (instancetype)shareDatabase{
    
    return [FMDBDao shareDatabase:nil];
}

+ (instancetype)shareDatabase:(NSString *)dbName{
    
    return [FMDBDao shareDatabase:dbName path:nil];
}
+(instancetype)shareDatabase:(NSString *)dbName path:(NSString *)dbPath{
    if (!dao) {
        NSString *path;
        if (!dbName) {
            dbName = @"FMDB.sqlite";
        }
        if (!dbPath) {
            path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject stringByAppendingString:dbName];
        }else{
            path = dbPath;
        }
        FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
        if ([fmdb open]) {
            dao = FMDBDao.new;
            dao.database = fmdb;
            dao.dbName = dbName;
        }
    }
    if (![dao.database open]) {
        NSLog(@"数据库打开失败");
        return nil;
    }else{
        if ([dao.database executeUpdate:CREAT_TABLE_TYPE]) {
            NSLog(@"CREAT_TABLE_TYPE创建数据库表成功 OR 数据库表已存在");
        }else{
            NSLog(@"CREAT_TABLE_TYPE创建数据库表失败");
        }
        if ([dao.database executeUpdate:CREAT_TABLE_NOTE]) {
            NSLog(@"CREAT_TABLE_NOTE创建数据库表成功 OR 数据库表已存在");
        }else{
            NSLog(@"CREAT_TABLE_NOTE创建数据库表失败");
        }
    }
    return dao;
}
//测试数据库插入数据操作
-(void)insertData{
    NSString *nameStr;
    NSInteger age;
    for (NSInteger i = 0; i < 1000; i++) {
        nameStr = [NSString stringWithFormat:@"Bourne-name%d",arc4random_uniform(1000)];
        age = arc4random_uniform(80) + 20;
        NSString *sql = [NSString stringWithFormat:CREAT_TABLE_PERSON_INSERT,nameStr,age];
        [_database executeUpdate:sql];
    }
    
}
#pragma mark -NOTE表增删改查
-(BOOL)insertNoteData:(Note *)note{
    NSString *sql = [NSString stringWithFormat:INSERT_TABLE_NOTE,note.content,note.dateString ,note.tid];
    if ([_database executeUpdate:sql]) {
        NSLog(@"NoteData数据插入成功");
        return TRUE;
    }else{
        NSLog(@"NoteData数据插入失败");
        return FALSE;
    }
}
-(NSMutableArray *)readNoteDataByTid:(NSInteger)tid{
    NSMutableArray *mArray = [NSMutableArray arrayWithCapacity:100];
    NSString *sql = [NSString stringWithFormat:QUERY_TABLE_NOTE_BYTID,tid];
    //执行查询
    FMResultSet *result = [_database executeQuery:sql];
    //遍历结果集
    while ([result next]) {
        int cid = [result intForColumn:@"cid"];
        NSString *content = [result stringForColumn:@"content"];
        NSString *date = [result stringForColumn:@"date"];
        int tid = [result intForColumn:@"tid"];
        Note *note = [[Note alloc]init];
        note.cid = cid;
        note.tid = tid;
        note.content = content;
        note.dateString = date;
        [mArray addObject:note];
    }
    NSLog(@"Note表中的数据总量是:%ld",[mArray count]);
    return mArray;
}
-(Note *)readNoteDataByCid:(NSInteger)cid{
    Note *note = [[Note alloc]init];;
    NSString *sql = [NSString stringWithFormat:QUERY_TABLE_NOTE_BYCID,cid];
    //执行查询
    FMResultSet *result = [_database executeQuery:sql];
    //遍历结果集
    while ([result next]) {
        int cid = [result intForColumn:@"cid"];
        NSString *content = [result stringForColumn:@"content"];
        NSString *date = [result stringForColumn:@"date"];
        int tid = [result intForColumn:@"tid"];
        note.cid = cid;
        note.tid = tid;
        note.content = content;
        note.dateString = date;
    }
    return note;
}
-(BOOL)deleteNoteDataByCid:(NSInteger)cid{
    NSString *sql = [NSString stringWithFormat:DELETE_TABLE_NOTE_BYID,cid];
    BOOL updateStatus = [_database executeUpdate:sql];
    if (updateStatus) {
        NSLog(@"deleteNoteDataByCid删除数据成功");
    }else{
        NSLog(@"deleteNoteDataByCid删除数据失败");
    }
    return updateStatus;
}

#pragma mark -Type表的增删改查
-(BOOL)insertTypeData:(Type *)type{
    NSString *sql = [NSString stringWithFormat:INSERT_TABLE_TYPE,type.tname];
    if ([_database executeUpdate:sql]) {
        NSLog(@"TypeData数据插入成功");
        return TRUE;
    }else{
        NSLog(@"TypeData数据插入失败");
        return FALSE;
    }
}
-(NSMutableArray *)readTypeDataByAll{
    NSMutableArray *mArray = [NSMutableArray arrayWithCapacity:100];
    //执行Type和note联合查询
    FMResultSet *resultAll = [_database executeQuery:QUERY_TABLE_TYPE];
    while ([resultAll next]) {
        NSString *tname = [resultAll stringForColumn:@"tname"];
        int tid = [resultAll intForColumn:@"tid"];
        int number = [resultAll intForColumn:@"num"];
        Type *type= [Type new];
        type.tname = tname;
        type.tid = tid;
        type.note1 = [NSString stringWithFormat:@"%d",number];
        [mArray addObject:type];
    }
    NSLog(@"Type表中的数据总量是:%ld",[mArray count]);
    return mArray;
}
-(BOOL)deleteTypeDataByTid:(NSInteger)tid{
    NSString *sql = [NSString stringWithFormat:DELETE_TABLE_TYPE_BYID,tid];
    BOOL updateStatus = [_database executeUpdate:sql];
    if (updateStatus) {
        NSLog(@"deleteTypeDataByTid删除数据成功");
    }else{
        NSLog(@"deleteTypeDataByTid删除数据失败");
    }
    return updateStatus;
}
@end
